package com.fiwizone.ultimateshoppinglist;

import java.text.SimpleDateFormat;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Simple shopping database access helper class. 
 */
public class ShoppingData 
{
    public static final String KEY_TITLE  	= "title";
    public static final String KEY_COST   	= "cost";
    public static final String KEY_LISTID 	= "list_id";
    public static final String KEY_ROWID  	= "_id";
    public static final String KEY_DATE	  	= "date";
    public static final String KEY_QUANTITY = "quantity";
    public static final String KEY_UNITCOST = "unit_cost";
    
    private static final String TAG = "ShoppingDbAdapter";
    
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    
    /**
     * Database creation sql statement
     */
    private static final String DATABASE_CREATE_LISTS =
            "create table lists (_id integer primary key autoincrement, "
                    + "title text not null, date integer not null);";
    
    private static final String DATABASE_CREATE_ITEMS =
        	"create table list_items (_id integer primary key autoincrement,"
					+ "list_id integer, title text not null, cost real not null,"
					+ "unit_cost real not null, quantity integer not null, date integer not null);";
    
    private static final String DATABASE_CREATE_PREVIOUS_ITEMS =
    	"create table previous_items (title text unique not null);";

    private static final String DATABASE_NAME 		 		  = "data";
    private static final String DATABASE_TABLE_LISTS 		  = "lists";
    private static final String DATABASE_TABLE_ITEMS 		  = "list_items";
    private static final String DATABASE_TABLE_PREVIOUS_ITEMS = "previous_items";
    
    private static final int DATABASE_VERSION = 3;

    private final Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DATABASE_CREATE_LISTS);
            db.execSQL(DATABASE_CREATE_ITEMS);
            db.execSQL(DATABASE_CREATE_PREVIOUS_ITEMS);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            
            if (oldVersion <= 2) {
            	db.execSQL("DROP TABLE IF EXISTS shopping_lists");
            	db.execSQL("DROP TABLE IF EXISTS shopping_items");
            }
            else {
            	db.execSQL("DROP TABLE IF EXISTS lists");
            	db.execSQL("DROP TABLE IF EXISTS list_items");
            	db.execSQL("DROP TABLE IF EXISTS previous_items");
            }
            
            onCreate(db);
        }
    }

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public ShoppingData(Context ctx) {
        this.mCtx = ctx;
    }

    /**
     * Open the shopping list database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public ShoppingData open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }
    
    /**
     * Close database helper
     */
    public void close() {
        mDbHelper.close();
    }
    
    
    /**
     * Format a date object to be stored in the database
     * 
     * @param d
     * @return
     */
    public static String formatDbDate(Date d) {
		SimpleDateFormat sdf = new SimpleDateFormat();
		sdf.applyPattern("yyyyMMdd");
		return sdf.format(d);
	}

    /**
     * Create a new shopping list using the title provided. If the list is
     * successfully created return the new rowId for that list, otherwise return
     * a -1 to indicate failure.
     * 
     * @param title the title of the list
     * @return rowId or -1 if failed
     */
    public long createList(String title) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_TITLE, title);
        initialValues.put(KEY_DATE, ShoppingData.formatDbDate(new Date()));

        return mDb.insert(DATABASE_TABLE_LISTS, null, initialValues);
    }
    
    /**
     * Create a new shopping list item using title, list id and cost
     * provided.
     * 
     * @param title
     * @param listId
     * @param cost
     * @return rowId or -1 if failed
     */
    public long createListItem(String title, long listId, float cost, long quantity) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_TITLE, title);        
        initialValues.put(KEY_QUANTITY, quantity);
        initialValues.put(KEY_UNITCOST, cost);  
        initialValues.put(KEY_COST, cost * quantity);
        initialValues.put(KEY_LISTID, listId);
        initialValues.put(KEY_DATE, ShoppingData.formatDbDate(new Date()));

        return mDb.insert(DATABASE_TABLE_ITEMS, null, initialValues);
    }

    /**
     * Delete the list with the given rowId
     * 
     * @param rowId id of list to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteList(long rowId) 
    {
    	boolean result = false;
    	
    	mDb.beginTransaction();
    	try {
    		// delete all items associated with this this list.
    		mDb.delete(DATABASE_TABLE_ITEMS, KEY_LISTID + "=" + rowId, null);
    		
    		result = mDb.delete(DATABASE_TABLE_LISTS, KEY_ROWID + "=" + rowId, null) > 0;
	     
    		mDb.setTransactionSuccessful();
    	} finally {
    		
    		mDb.endTransaction();
    	}
    	
        return result;        
    }
    
    /**
     * Delete a list item with the given rowId
     * 
     * @param rowId
     * @return
     */
    public boolean deleteListItem(long rowId) 
    {
        return mDb.delete(DATABASE_TABLE_ITEMS, KEY_ROWID + "=" + rowId, null) > 0;
    }

    /**
     * Return a Cursor over the list of all lists in the database
     * 
     * @return Cursor over all lists
     */
    public Cursor fetchAllLists() {

        return mDb.query(DATABASE_TABLE_LISTS, new String[] {KEY_ROWID, KEY_TITLE, KEY_DATE}
        	, null, null, null, null, KEY_DATE + " DESC");
    }
    
    /**
     * Return a Cursor over the list of all the items for a list in the database
     * 
     * @param listId
     * @return
     */
    public Cursor fetchAllListItems(long listId) {

        return mDb.query(DATABASE_TABLE_ITEMS, new String[] {KEY_ROWID, KEY_TITLE, KEY_COST}
        	, KEY_LISTID + "=" + listId, null, null, null, KEY_DATE + " DESC");
    }
    
    /**
     * Fetch the total amount of all the items in a list.
     * 
     * @param listId
     * @return
     */
    public Cursor fetchListTotal(long listId)
    {
    	Cursor mCursor =
    		mDb.rawQuery("select sum(cost) as cost from " + DATABASE_TABLE_ITEMS +  " where " + KEY_LISTID + " = " + listId, new String [] {});
    	
    	if (mCursor != null) {
            mCursor.moveToFirst();
        }
    	
        return mCursor;
    }

    /**
     * Return a Cursor positioned at the list that matches the given rowId
     * 
     * @param rowId id of list to retrieve
     * @return Cursor positioned to matching list, if found
     * @throws SQLException if list could not be found/retrieved
     */
    public Cursor fetchList(long rowId) throws SQLException {

        Cursor mCursor =
                mDb.query(true, DATABASE_TABLE_LISTS, new String[] {KEY_ROWID,
                        KEY_TITLE}, KEY_ROWID + "=" + rowId, null,
                        null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        
        return mCursor;
    }
    
    /**
     * Return a Cursor positioned at the shopping list item that matches the given rowId
     * 
     * @param rowId
     * @return Cursor positioned to matching list item, if found
     * @throws SQLException if list item could not be found/retrieved
     */
    public Cursor fetchListItem(long rowId) throws SQLException {

        Cursor mCursor =

                mDb.query(true, DATABASE_TABLE_ITEMS, new String[] {KEY_ROWID,
                        KEY_TITLE, KEY_LISTID, KEY_COST, KEY_QUANTITY, KEY_UNITCOST}, 
                        KEY_ROWID + "=" + rowId, null,
                        null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        
        return mCursor;

    }

    /**
     * Update the list using the details provided.
     * 
     * @param rowId id of list to update
     * @param title value to set list title to
     * @return true if the list was successfully updated, false otherwise
     */
    public boolean updateList(long rowId, String title) {
        ContentValues args = new ContentValues();
        args.put(KEY_TITLE, title);
        args.put(KEY_DATE, ShoppingData.formatDbDate(new Date()));

        return mDb.update(DATABASE_TABLE_LISTS, args, KEY_ROWID + "=" + rowId, null) > 0;
    }
    
    /**
     * Update the shopping list item using the details provided.
     * 
     * @param rowId
     * @param title
     * @param cost
     * @return true if the item was successfully update, false otherwise
     */
    public boolean updateListItem(long rowId, String title, float cost, long quantity) {
        ContentValues args = new ContentValues();
        args.put(KEY_TITLE, title);
        args.put(KEY_UNITCOST, cost);
        args.put(KEY_QUANTITY, quantity);
        args.put(KEY_COST, cost * quantity);
        args.put(KEY_DATE, ShoppingData.formatDbDate(new Date()));

        return mDb.update(DATABASE_TABLE_ITEMS, args, KEY_ROWID + "=" + rowId, null) > 0;
    }
}
